from backend.utils.db import get_db_connection
from datetime import datetime
from backend.utils.db import get_db_connection


class OrderService:
    def get_order_details(self, order_id, user_id):
        try:
            with get_db_connection() as (cursor, conn):
                sql = """
                      SELECT o.order_id, \
                             p.name AS product_name, \
                             o.status,
                             o.shipping_address, \
                             o.contact_phone
                      FROM orders o
                               JOIN products p ON o.product_id = p.product_id
                      WHERE o.order_id = %s \
                        AND o.user_id = %s \
                      """
                cursor.execute(sql, (order_id, user_id))
                result = cursor.fetchone()

                # 确保返回字典格式
                if result:
                    return {
                        'order_id': result.get('order_id', ''),
                        'product_name': result.get('product_name', ''),
                        'status': result.get('status', ''),
                        'shipping_address': result.get('shipping_address', ''),
                        'contact_phone': result.get('contact_phone', '')
                    }
                return None
        except Exception as e:
            print(f"查询订单详情失败: {e}")
            import traceback
            traceback.print_exc()
            return None

    @staticmethod
    def get_order_by_id(order_id, user_id=None):
        """根据订单ID获取订单信息"""
        try:
            # 必须使用with语句调用上下文管理器
            with get_db_connection() as (cursor, conn):
                query = """
                        SELECT o.*, p.name as product_name
                        FROM orders o
                                 JOIN products p ON o.product_id = p.product_id
                        WHERE o.order_id = %s \
                        """
                params = [order_id]

                if user_id:
                    query += " AND o.user_id = %s"
                    params.append(user_id)

                cursor.execute(query, tuple(params))
                return cursor.fetchone()  # 返回字典或None
        except Exception as e:
            print(f"获取用户订单失败: {e}")
            return None

    @staticmethod
    def get_user_orders(user_id, page=1, limit=10, status=None):
        """获取用户的订单列表"""
        try:
            with get_db_connection() as (cursor, conn):
                offset = (page - 1) * limit
                query = """
                        SELECT o.*, p.name as product_name
                        FROM orders o
                                 JOIN products p ON o.product_id = p.product_id
                        WHERE o.user_id = %s \
                        """
                count_query = "SELECT COUNT(*) as total FROM orders WHERE user_id = %s"

                params = [user_id]
                count_params = [user_id]

                if status and status != 'all':
                    query += " AND o.status = %s"
                    count_query += " AND status = %s"
                    params.append(status)
                    count_params.append(status)

                query += " ORDER BY o.order_time DESC LIMIT %s OFFSET %s"
                params.extend([limit, offset])

                cursor.execute(query, tuple(params))
                orders = cursor.fetchall()

                cursor.execute(count_query, tuple(count_params))
                total = cursor.fetchone()['total']

                return {
                    "orders": orders,
                    "total": total,
                    "page": page,
                    "limit": limit,
                    "pages": (total + limit - 1) // limit
                }
        except Exception as e:
            print(f"获取用户订单失败: {e}")
            return {
                "orders": [],
                "total": 0,
                "page": page,
                "limit": limit,
                "pages": 0
            }

    def order_exists(self, order_id):
        """检查订单是否存在（不限于当前用户）"""
        try:
            with get_db_connection() as (cursor, conn):
                sql = "SELECT order_id FROM orders WHERE order_id = %s"
                cursor.execute(sql, (order_id,))
                return cursor.fetchone() is not None
        except Exception as e:
            print(f"检查订单存在失败: {str(e)}")
            return False

    @staticmethod
    def get_order_by_id(order_id, user_id=None):
        """根据订单ID获取订单信息"""
        try:
            with get_db_connection() as (cursor, conn):  # 使用正确的上下文管理器调用
                query = """
                        SELECT o.*, p.name as product_name
                        FROM orders o
                                 JOIN products p ON o.product_id = p.product_id
                        WHERE o.order_id = %s
                        """
                params = [order_id]
                if user_id:
                    query += " AND o.user_id = %s"
                    params.append(user_id)

                cursor.execute(query, tuple(params))
                order = cursor.fetchone()  # 正常应为字典或None

                # 新增：验证返回类型
                if order is not None and not isinstance(order, dict):
                    print(f"订单查询返回非字典类型: {type(order)}")
                    return None
                return order
        except Exception as e:
            print(f"获取订单信息失败: {e}")
            return None  # 失败时明确返回None，而非字符串

    def get_user_recent_orders(self, user_id, limit=5):
        """获取用户最近的订单"""
        try:
            with get_db_connection() as (cursor, conn):
                sql = """
                      SELECT o.order_id, p.name AS product_name, o.status
                      FROM orders o
                               JOIN products p ON o.product_id = p.product_id
                      WHERE o.user_id = %s
                      ORDER BY o.order_time DESC
                          LIMIT %s \
                      """
                cursor.execute(sql, (user_id, limit))
                return cursor.fetchall()
        except Exception as e:
            print(f"获取用户最近订单失败: {e}")
            return []

    @staticmethod
    def get_user_recent_orders(user_id, limit=3):
        """获取用户最近的订单"""
        try:
            with get_db_connection() as (cursor, conn):
                cursor.execute("""
                               SELECT o.*, p.name as product_name
                               FROM orders o
                                        JOIN products p ON o.product_id = p.product_id
                               WHERE o.user_id = %s
                               ORDER BY o.order_time DESC LIMIT %s
                               """, (user_id, limit))
                return cursor.fetchall()
        except Exception as e:
            print(f"获取用户最近订单失败: {e}")
            return []

    @staticmethod
    def create_order(order_data, user_id):
        """创建新订单"""
        try:
            with get_db_connection() as (cursor, conn):
                # 生成订单ID
                order_id = f"ORD{datetime.now().strftime('%Y%m%d%H%M%S')}"

                # 检查产品库存
                cursor.execute("SELECT stock FROM products WHERE product_id = %s",
                               (order_data['product_id'],))
                product = cursor.fetchone()

                if not product or product['stock'] < order_data['quantity']:
                    return False, "产品库存不足"

                # 获取产品价格
                cursor.execute("SELECT price, name FROM products WHERE product_id = %s",
                               (order_data['product_id'],))
                product_info = cursor.fetchone()

                total_price = product_info['price'] * order_data['quantity']

                # 创建订单
                cursor.execute("""
                               INSERT INTO orders
                               (order_id, user_id, product_id, quantity, total_price, status,
                                shipping_address, contact_phone, order_time)
                               VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
                               """, (
                                   order_id, user_id, order_data['product_id'],
                                   order_data['quantity'], total_price, 'pending',
                                   order_data['shipping_address'], order_data['contact_phone'],
                                   datetime.now()
                               ))

                # 减少库存
                cursor.execute("""
                               UPDATE products
                               SET stock = stock - %s
                               WHERE product_id = %s
                               """, (order_data['quantity'], order_data['product_id']))

                conn.commit()
                return True, f"订单创建成功，订单号: {order_id}"
        except Exception as e:
            # 使用上下文管理器时，异常发生时会自动回滚
            print(f"创建订单失败: {str(e)}")
            return False, "创建订单失败"

    @staticmethod
    def update_order_status(order_id, status, admin_id=None):
        """更新订单状态"""
        try:
            with get_db_connection() as (cursor, conn):
                # 检查订单是否存在
                cursor.execute("SELECT * FROM orders WHERE order_id = %s", (order_id,))
                if not cursor.fetchone():
                    return False, "订单不存在"

                # 更新状态
                update_fields = {
                    "status": status
                }

                # 根据状态更新相应时间
                if status == 'paid':
                    update_fields["payment_time"] = datetime.now()
                elif status == 'shipped':
                    update_fields["shipping_time"] = datetime.now()
                elif status == 'delivered':
                    update_fields["delivery_time"] = datetime.now()

                # 构建更新语句
                set_clause = ", ".join([f"{k} = %s" for k in update_fields.keys()])
                values = list(update_fields.values()) + [order_id]

                cursor.execute(f"""
                    UPDATE orders 
                    SET {set_clause}
                    WHERE order_id = %s
                """, tuple(values))

                conn.commit()
                return True, f"订单状态已更新为: {status}"
        except Exception as e:
            print(f"更新订单状态失败: {str(e)}")
            return False, "更新订单状态失败"

    @staticmethod
    def cancel_order(order_id, user_id):
        """取消订单"""
        try:
            with get_db_connection() as (cursor, conn):
                # 检查订单是否存在且属于该用户
                cursor.execute("""
                               SELECT o.*, p.name as product_name
                               FROM orders o
                                        JOIN products p ON o.product_id = p.product_id
                               WHERE o.order_id = %s
                                 AND o.user_id = %s
                               """, (order_id, user_id))
                order = cursor.fetchone()

                if not order:
                    return False, "订单不存在或无权操作"

                # 只有未发货的订单可以取消
                if order['status'] in ['shipped', 'delivered']:
                    return False, "订单已发货，无法取消"

                # 更新订单状态
                cursor.execute("""
                               UPDATE orders
                               SET status         = 'cancelled',
                                   cancelled_time = %s
                               WHERE order_id = %s
                               """, (datetime.now(), order_id))

                # 恢复库存
                cursor.execute("""
                               UPDATE products
                               SET stock = stock + %s
                               WHERE product_id = %s
                               """, (order['quantity'], order['product_id']))

                conn.commit()
                return True, "订单已取消"
        except Exception as e:
            print(f"取消订单失败: {str(e)}")
            return False, "取消订单失败"

    @staticmethod
    def fuzzy_search_orders(user_id, keyword):
        """根据关键词模糊查询用户订单"""
        try:
            with get_db_connection() as (cursor, conn):
                sql = """
                      SELECT order_id, status
                      FROM orders
                      WHERE user_id = %s
                        AND (order_id LIKE %s OR product_id LIKE %s) LIMIT 5
                      """
                cursor.execute(sql, (user_id, f"%{keyword}%", f"%{keyword}%"))
                return cursor.fetchall()
        except Exception as e:
            print(f"模糊查询订单失败: {e}")
            return []

    # 在OrderService中添加
    def get_order_with_details(self, order_id):
        with get_db_connection() as (cursor, _):
            cursor.execute("""
                           SELECT o.*, p.name as product_name
                           FROM orders o
                                    JOIN products p ON o.product_id = p.product_id
                           WHERE o.order_id = %s
                           """, (order_id,))
            return cursor.fetchone()